--删除出版社表
drop table books_21215;
drop table orders;
drop table orders_21215;
drop table Customers;
drop table publishers_21215;
drop table orderitem_21215;
-- 顾客
Create table Customers_21215(
    Customer_id number(4) primary key,
    Name char(20) not null,
    Phone varchar2(50) not null,
    Email varchar2(50),
    Address varchar2(200),
    Code varchar2(10)
)tablespace sgl21215;
-- 出版社
create table publishers_21215(
    publisher_id number(2) primary key,
    name char(50),
    contact char(10),
    phone varchar2(50)
)tablespace sgl21215;
-- 图书表
create table books_21215(
    ISBN varchar2(50) primary key,
    title varchar2(50),
    author varchar2(50),
    pubdate date,
    publisher_id number(2) references publishers_21215(publisher_id),
    cost number(6,2),
    retail number(6,2),
    category varchar2(50)
)tablespace sgl21215;

--订单表
create table orders_21215
(
   order_id           NUMBER(4)             primary key,
   customer_id        number(4) references Customers_21215(customer_id),
   orderdate          date                  not null,
   shipdate           date,
   shipaddress        VARCHAR2(200),
   shipcode           VARCHAR2(10)   
)tablespace sgl21215;
-- 订单详情表
create table orderitem_21215(
    order_id number(4) references orders_21215(order_id),
    item_id number(4),
    ISBN varchar2(50) not null,
    quantity number(4),
    primary key(order_id,item_id)
)tablespace sgl21215;
-- 创建索引
create index idx_orderitem_ISBN_21215 on orderitem(ISBN);
-- 删除索引
drop index idx_orderitem_ISBN_21215;
-- 创建一个视图customers _ book，描述客户与订单的详细信息，
-- 包括客户编号、客户名称、订购图书的ISBN、书名、图书数量、订货日期、发货日期等。
-- 四表连接：Customers_21215、orders_21215、 orderitem_21215、books_21215（寻找外键关系）
CREATE VIEW customers_book_21215 AS
SELECT
Customers_21215.Customer_id,
Customers_21215.Name,
books_21215.ISBN,
books_21215.title, 
orderitem_21215.quantity,
orders_21215.orderdate,
orders_21215.shipdate
FROM
Customers_21215
JOIN
orders_21215 ON Customers_21215.Customer_id = orders_21215.customer_id
JOIN
orderitem_21215 ON orders_21215.order_id = orderitem_21215.order_id
JOIN 
books_21215 on orderitem_21215.ISBN = books_21215.ISBN;
-- 删除视图
drop VIEW customers_book_21215;
-- 创建序列
create sequence seq_orders_21215 
start with 1000
increment by 1
nocache;-- 不缓存过期数据
--为customers_21215插入5条数据
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1000, 'John Doe', '123-456-7890', 'john.doe@example.com', '123 Main St', 'AB123');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1001, 'Jane Smith', '555-555-5555', 'jane.smith@example.com', '456 Elm St', 'CD456');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1002, 'Mike Johnson', '987-654-3210', 'mike.johnson@example.com', '789 Oak St', 'EF789');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1003, 'Sarah Brown', '999-888-7777', 'sarah.brown@example.com', '321 Maple St', 'GH321');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1004, 'Chris Davis', '444-777-6666', 'chris.davis@example.com', '987 Pine St', 'HI987');
--为orders_21215用序列的方式插入几条数据
INSERT INTO orders_21215 (order_id, customer_id, orderdate, shipdate, shipaddress, shipcode)
VALUES (seq_orders_21215.NEXTVAL, 1000, SYSDATE, SYSDATE, '123 Main St', 'AB123');
INSERT INTO orders_21215 (order_id, customer_id, orderdate, shipdate, shipaddress, shipcode)
VALUES (seq_orders_21215.NEXTVAL, 1001, SYSDATE, SYSDATE, '456 Elm St', 'CD456');
INSERT INTO orders_21215 (order_id, customer_id, orderdate, shipdate, shipaddress, shipcode)
VALUES (seq_orders_21215.NEXTVAL, 1002, SYSDATE, SYSDATE, '789 Oak St', 'EF789');
--查询订单表
select * from orders_21215;
create table publishers_21215(
    publisher_id number(2) primary key,
    name char(50),
    contact char(10),
    phone varchar2(50)
)tablespace sgl21215;
-- 为publishers_21215插入5条信息
INSERT INTO publishers_21215 (publisher_id, name, contact, phone)
VALUES (1, 'HarperCollins Publishers', 'John', '+1234567890');
INSERT INTO publishers_21215 (publisher_id, name, contact, phone)
VALUES (2, 'Macmillan Publishers', 'Jane', '+0987654321');
INSERT INTO publishers_21215 (publisher_id, name, contact, phone)
VALUES (3, 'Penguin Random House', 'Mike', '+5551234567');
INSERT INTO publishers_21215 (publisher_id, name, contact, phone)
VALUES (4, 'W. W. Norton & Company', 'Sarah', '+4445556667');
INSERT INTO publishers_21215 (publisher_id, name, contact, phone)
VALUES (5, 'Simon & Schuster', 'Chris', '+3334445556');
--根据以上出版社信息，为books_21215插入5条数据，以下是图书表信息
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780062386821', 'To Kill a Mockingbird', 'Harper Lee', TO_DATE('2021-01-01', 'YYYY-MM-DD'), 1, 29.99, 34.99, 'Fiction');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780374534538', 'The Catcher in the Rye', 'J.D. Salinger', TO_DATE('2021-02-01', 'YYYY-MM-DD'), 2, 35.99, 41.99, 'Teen & Young Adult');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780593087876', 'Animal Farm', 'George Orwell', TO_DATE('2021-03-01', 'YYYY-MM-DD'), 3, 22.99, 26.99, 'Fiction');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780735697443', 'Brave New World', 'Aldous Huxley', TO_DATE('2021-04-01', 'YYYY-MM-DD'), 4, 45.99, 51.99, 'Science & Nature');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780446584606', 'The Great Gatsby', 'F. Scott Fitzgerald', TO_DATE('2021-05-01', 'YYYY-MM-DD'), 5, 17.99, 21.99, 'Fiction');
-- 根据以上Customers_21215，books_21215， orders_21215信息，为orderitem_21215插入5条数据，以下是订单详情表信息
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1000, 5001, '9780062386821', 3);
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1001, 5002, '9780374534538', 4);
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1002, 5003, '9780593087876', 2);
--orders_21215就3条数据这里多余
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1004, 5004, '9780735697443', 1);
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1005, 5005, '9780446584606', 5);
select * from orders_21215；
-- 删除数据 
delete orderitem_21215 where order_id = 1001;
select * from books_21215;
select * from Customers_21215;
--

--
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1000, 'John Doe', '123-456-7890', 'john.doe@example.com', '123 Main St', 'AB123');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1001, 'Jane Smith', '555-555-5555', 'jane.smith@example.com', '456 Elm St', 'CD456');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1002, 'Mike Johnson', '987-654-3210', 'mike.johnson@example.com', '789 Oak St', 'EF789');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1003, 'Sarah Brown', '999-888-7777', 'sarah.brown@example.com', '321 Maple St', 'GH321');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1004, 'Chris Davis', '444-777-6666', 'chris.davis@example.com', '987 Pine St', 'HI987');
select Customer_id,Name  from Customers_21215 where Address = '123 Main St' or Address = '456 Elm St' order by Name;
CUSTOMER_ID NAME
----------- --------------------
       1001 Jane Smith
       1000 John Doe
--
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780062386821', 'To Kill a Mockingbird', 'Harper Lee', TO_DATE('2021-01-01', 'YYYY-MM-DD'), 1, 29.99, 34.99, 'Fiction');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780374534538', 'The Catcher in the Rye', 'J.D. Salinger', TO_DATE('2021-02-01', 'YYYY-MM-DD'), 2, 35.99, 41.99, 'Teen & Young Adult');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780593087876', 'Animal Farm', 'George Orwell', TO_DATE('2021-03-01', 'YYYY-MM-DD'), 3, 22.99, 26.99, 'Fiction');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780735697443', 'Brave New World', 'Aldous Huxley', TO_DATE('2021-04-01', 'YYYY-MM-DD'), 4, 45.99, 51.99, 'Science & Nature');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780446584606', 'The Great Gatsby', 'F. Scott Fitzgerald', TO_DATE('2021-05-01', 'YYYY-MM-DD'), 5, 17.99, 21.99, 'Fiction');
--
select * from books_21215;
SELECT * FROM books_21215 WHERE title LIKE 'A%m';
ISBN                                               TITLE                                              AUTHOR                                             PUBDATE     PUBLISHER_ID     COST   RETAIL CATEGORY
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ------------ -------- -------- --------------------------------------------------
9780593087876                                      Animal Farm                                        George Orwell                                      2021/3/1               3    22.99    26.99 Fiction

--
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1000, 'John Doe', '123-456-7890', 'john.doe@example.com', '123 Main St', 'AB123');
INSERT INTO customers_21215 (Customer_id, Name, Phone, Email, Address, Code)
VALUES (1001, 'Jane Smith', '555-555-5555', 'jane.smith@example.com', '456 Elm St', 'CD456');
--
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780062386821', 'To Kill a Mockingbird', 'Harper Lee', TO_DATE('2021-01-01', 'YYYY-MM-DD'), 1, 29.99, 34.99, 'Fiction');
INSERT INTO books_21215 (ISBN, title, author, pubdate, publisher_id, cost, retail, category)
VALUES ('9780374534538', 'The Catcher in the Rye', 'J.D. Salinger', TO_DATE('2021-02-01', 'YYYY-MM-DD'), 2, 35.99, 41.99, 'Teen & Young Adult');
--
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1000, 5001, '9780062386821', 3);
INSERT INTO orderitem_21215 (order_id, item_id, ISBN, quantity)
VALUES (1001, 5002, '9780374534538', 4);
--
SELECT
Customers_21215.Customer_id,
Customers_21215.Name,
books_21215.ISBN,
books_21215.title, 
orderitem_21215.quantity,
orders_21215.orderdate,
orders_21215.shipdate
FROM
Customers_21215
JOIN
orders_21215 ON Customers_21215.Customer_id = orders_21215.customer_id
JOIN
orderitem_21215 ON orders_21215.order_id = orderitem_21215.order_id
JOIN 
books_21215 on orderitem_21215.ISBN = books_21215.ISBN;
-- 客户(Customer_id,Name)，图书(category)，详情
SELECT
books_21215.ISBN,
books_21215.title
FROM
Customers_21215
JOIN
orders_21215 ON Customers_21215.Customer_id = orders_21215.customer_id
JOIN
orderitem_21215 ON orders_21215.order_id = orderitem_21215.order_id
JOIN 
books_21215 on orderitem_21215.ISBN = books_21215.ISBN where category = 'Fiction';
ISBN                                               TITLE
-------------------------------------------------- --------------------------------------------------
9780062386821                                      To Kill a Mockingbird
9780593087876                                      Animal Farm

--
select * from books_21215;
desc books_21215;
--
select * from books_21215 b1 where b1.retail < (select avg(b2.retail) from books_21215 b2 where b1.category = b2.category);
ISBN                                               TITLE                                              AUTHOR                                             PUBDATE     PUBLISHER_ID     COST   RETAIL CATEGORY
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ------------ -------- -------- --------------------------------------------------
9780593087876                                      Animal Farm                                        George Orwell                                      2021/3/1               3    22.99    26.99 Fiction
9780446584606                                      The Great Gatsby                                   F. Scott Fitzgerald                                2021/5/1               5    17.99    21.99 Fiction
--
SELECT
Customers_21215.Customer_id,
Customers_21215.name,
sum(quantity),
sum(quantity*retail)
FROM
Customers_21215
JOIN
orders_21215 ON Customers_21215.Customer_id = orders_21215.customer_id
JOIN
orderitem_21215 ON orders_21215.order_id = orderitem_21215.order_id
JOIN 
books_21215 on orderitem_21215.ISBN = books_21215.ISBN group by Customers_21215.Customer_id,Customers_21215.name;

CUSTOMER_ID NAME                 SUM(QUANTITY) SUM(QUANTITY*RETAIL)
----------- -------------------- ------------- --------------------
       1001 Jane Smith                       4               167.96
       1000 John Doe                         3               104.97
       1002 Mike Johnson                     2                53.98
--
create tablespace booktbs1 datafile 'D:/sgl21215_01.dbf' size 50m;
create tablespace booktbs2 datafile 'D:/sgl21215_02.dbf' size 50m;
create user Tom 
identified by Tom
default tablespace users
temporary tablespace temp
quota 10m on users
quota 50m on booktbs1;
--
create user Joan
identified by Joan
default tablespace booktbs2
temporary tablespace temp
quota 10m on users
quota 20m on booktbs2
account lock;
--
grant create session to Tom;
grant create session to Joan;
alter user Joan identified by Joan account unlock;
--
grant create table,create view to Tom with admin Option;